package com.wenguo.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.wenguo.Constants;

public final class DBLoader {
	
	private final static Logger logger = Logger.getLogger(DBLoader.class);

	private static DBLoader dbLoader;
	private DB db;


	private DBLoader() {
		if (null == db) {
			db = new DB();
		}
	}

	public static DBLoader getInstance() {
		if (dbLoader == null) {
			dbLoader = new DBLoader();
		}
		return dbLoader;
	}

	public void load() {
		logger.info("<DB>DB Initialization</DB>");
		db.testDB();
		logger.info("<DB>DB over</DB>");
	}
	
	private static class DB {
		
		private final static Logger logger = Logger.getLogger(DB.class);
		
		private Properties dbProp;
		private Properties sqlProp;
		
		private DB() {
			dbProp = ConfigLoader.getInstance().getJdbcProp();
			sqlProp = ConfigLoader.getInstance().getSqlProp();
		}
		
		protected void testDB() {
			Connection conn = null;
			Statement st = null;
			String sql = null;
			
			try {
				String dirver = dbProp.getProperty(Constants.sql.JDBC_DIRVER);
				String url = dbProp.getProperty(Constants.sql.JDBC_URL);
				
				try {
					 Class.forName(dirver);
				} catch (ClassNotFoundException e) {
					logger.error("<EXCEPTION>JDBC Dirver</EXCEPTION>:" + e);
					e.printStackTrace();
				}
				
				conn = DriverManager.getConnection(url);
				st = conn.createStatement();
				try {
					sql = sqlProp.getProperty(Constants.sql.DB_TEST);
					logger.info("<SQL>execute: " + sql + "</SQL>");
					st.execute(sql);
					logger.info("<TEST>DB is exist</TEST>");
				} catch (Exception e) {
					logger.error("<CREATE>if throw exception, means the db is not exist.</CREATE>:" + e);
					this.createDB(st, sqlProp);
				}

				conn.commit();
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("<EXCEPTION>DB Create</EXCEPTION>" + e);
			}finally{
				try {
					if(null != conn) {
						conn.close();
					}
					if(null != st) {
						st.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		protected void createDB(Statement st, Properties sqlProp) {

			this.executeSQL(st, sqlProp, Constants.sql.DB_TABLE_GROUP);
			this.executeSQL(st, sqlProp, Constants.sql.DB_TABLE_GROUP_INIT);
			this.executeSQL(st, sqlProp, Constants.sql.DB_TABLE_USERINFO);
			this.executeSQL(st, sqlProp, Constants.sql.DB_TABLE_USERINFO_INIT);
			this.executeSQL(st, sqlProp, Constants.sql.DB_TABLE_LOGINFO);

		}
		
		public void executeSQL(Statement st, Properties sqlProp, String key) {
			try {
				String sql = sqlProp.getProperty(key);
				logger.info("<EXECUTE SQL>" + sql + "</EXECUTE SQL>");
				st.execute(sql);
			} catch (SQLException e) {
				logger.error("<EXECUTE SQL>" + e.getMessage() + "</EXECUTE SQL>:" + e);
			}
		}
	}
}
